const { autoCommit } = require("oracledb")
const db = require("../../lib/conn_oracle")

//获取检测记录数据
exports.getJcData = async (req,res)=>{
    console.log(req.body);
    let {form} = req.body
    let {jcYear,lines,stations,specialties,subsys,isFinish,status,description} = form
    let resDatas
    let msg
    let resStatus
    let jcSqlDatas = []
    //年份条件    
    let fullJcYear 
    fullJcYear = `${jcYear}-12-31`   
    jcSqlDatas.push(fullJcYear)
    // console.log(fullJcYear) 
    let jcYearWhere = `schedfinish = trunc(to_timestamp(:fullJcYear,'yyyy-mm-dd hh24:mi:ss.ff'))`
    //线路条件
    let linesWhere
    if(lines && lines.length > 1){
        linesWhere = `and cust_linenum in (`
        for (let i = 0; i < lines.length; i++)
        linesWhere += (i > 0) ? ", :" + i : ":" + i
        linesWhere += ")"
        jcSqlDatas.push(...lines)
    }else if(lines && lines.length === 1){
        linesWhere = `and cust_linenum = :lines` 
        jcSqlDatas.push(...lines)
    }else{linesWhere = ""}
    //站点条件
    let stationsWhere
    if(stations && stations.length > 1){
        stationsWhere = `and location in (`
        for (let i = 0; i < stations.length; i++)
        stationsWhere += (i > 0) ? ", :" + i : ":" + i 
        stationsWhere += ")"
        jcSqlDatas.push(...stations)
    }else if(stations && stations.length === 1){
        stationsWhere = `and location = :stations` 
        jcSqlDatas.push(...stations)
    }else{stationsWhere = ""}
    //专业条件
    let specialtiesWhere
    if(specialties && specialties.length > 1){
        specialtiesWhere = `and cust_specnum in (`
        for (let i = 0; i < specialties.length; i++)
        specialtiesWhere += (i > 0)? ", :" + i : ":" + i
        specialtiesWhere += ")"  
        jcSqlDatas.push(...specialties)
    }else if(specialties && specialties.length === 1){
        specialtiesWhere = `and cust_specnum = :specialties`
        jcSqlDatas.push(...specialties)
    }else{specialtiesWhere = ""}
    //子系统条件
    let subsysWhere
    if(subsys && subsys.length > 1){
        subsysWhere = `and cust_subsys in (`
        for (let i = 0; i < subsys.length; i++)
        subsysWhere += (i > 0)? ", :" + i : ":" + i  
        subsysWhere += ")"
        jcSqlDatas.push(...subsys)
    }else if(subsys && subsys.length === 1){
        subsysWhere = `and cust_subsys = :subsys`
        jcSqlDatas.push(...subsys)
    }else{subsysWhere = ""}
    //完成条件
    let isFinishWhere
    if(isFinish === ""){
        isFinishWhere = ""
    }else if(!Number(isFinish)){
        isFinishWhere = `and finishdate is null`
    }else if(Number(isFinish)){
        isFinishWhere = `and finishdate is not null`
    }
    //状态条件
    let statusWhere 
    if(status === 1 || status === 0){
        statusWhere = `and status = :status`
        jcSqlDatas.push(status)
    }else{statusWhere = ""}
    //描述条件
    let descLike  
    let descWhere 
    if(description.length > 0){
        descLike = `%${description}%`
        descWhere = `and description like :descLike`
        jcSqlDatas.push(descLike)
    }else{
        descLike = ""
        descWhere = ""
    }
    //查询SQL
    let jcSql = `select * from c##wxw.xfjiance 
                 where 
                 ${jcYearWhere}  
                 ${linesWhere} 
                 ${stationsWhere} 
                 ${specialtiesWhere} 
                 ${subsysWhere} 
                 ${isFinishWhere} 
                 ${statusWhere} 
                 ${descWhere} 
                 order by schedfinish,devicenum
                 `
    // console.log(jcSqlDatas)
    // console.log(jcSql)
    try{
        let jcDataInfo = jcSqlDatas.length ? await db.search(jcSql,jcSqlDatas) : await db.search2(jcSql)        
        resDatas = {jcDataInfo}
        msg = resDatas.jcDataInfo.errorNum ? "查询失败" : "查询成功"
        resStatus = resDatas.jcDataInfo.errorNum? 0 : 1
        res.json({
            status:resStatus,
            msg,
            resDatas 
        })
    }catch(err){
        // console.log(err)
        res.json({
            status:0,
            msg:"查询失败",
            resDatas:{err}
        })
    }

}

//批量勾选变更单据状态
exports.changeJcDataStatus = async (req,res)=>{
    // console.log(req.body);
    let {type,form} = req.body
    let submitDatas = form
    let options = {
        autoCommit: true
    }
    let resDatas
    let msg
    let msgType = type === "batchChangeStatus" ? "批量修改状态" : ""
    let resStatus
    let changeSql = `update c##wxw.xfjiance 
                     set status = :status 
                     where wonum = :wonum
                    `
    try{
        let newJcDatas = await db.writeManyTab(changeSql,submitDatas,options)
        resDatas = {newJcDatas}
        msg = resDatas.newJcDatas.errorNum ? `${msgType}失败` : `${msgType}修改成功`
        resStatus = resDatas.newJcDatas.errorNum ? 0 : 1
        res.json({
            status:resStatus,
            msg,
            resDatas
        })
    }catch(err){
        console.log(err)
        res.json({
            status:0,
            msg:`${msgType}失败`,
            resDatas:{err}
        })
    }

}

//单个处理检测记录
exports.editJcData = async (req,res)=>{
    // console.log(req.body);
    let {type,form} = req.body
    let {WONUM,DESCRIPTION,AREA,AREA2,ISPASS,FINISHDATE,PERSON,PERSON2,PERSON3,REMARK,CUST_LINENUM,CUST_SPECNUM,SPEC_DESC,
        CUST_SUBSYS,SUB_DESC,LOCATION,PERSONGROUP,PGNAME,SCHEDFINISH,DEVICENUM,SBDESC,STATUS
    } = form
    let resDatas
    let msg
    let msgType
    let resStatus
    let editData 
    let editSql 
    if(type === "update"){
        msgType = "修改"
        editData =  FINISHDATE === "" ? [DESCRIPTION,AREA,AREA2,ISPASS,PERSON,PERSON2,PERSON3,REMARK,CUST_LINENUM,CUST_SPECNUM,SPEC_DESC,
                                CUST_SUBSYS,SUB_DESC,LOCATION,PERSONGROUP,PGNAME,SCHEDFINISH,DEVICENUM,SBDESC,Number(STATUS),Number(WONUM)]
                            : [DESCRIPTION,AREA,AREA2,ISPASS,FINISHDATE,PERSON,PERSON2,PERSON3,REMARK,CUST_LINENUM,CUST_SPECNUM,SPEC_DESC,
                                CUST_SUBSYS,SUB_DESC,LOCATION,PERSONGROUP,PGNAME,SCHEDFINISH,DEVICENUM,SBDESC,Number(STATUS),Number(WONUM)]
        editSql = FINISHDATE === "" ? `update c##wxw.xfjiance 
                            set description = :DESCRIPTION,
                            area = :AREA,
                            area2 = :AREA2,
                            ispass = :ISPASS,
                            person = :PERSON,
                            person2 = :PERSON2,
                            person3 = :PERSON3,
                            remark = :REMARK,
                            cust_linenum = :CUST_LINENUM,
                            cust_specnum = :CUST_SPECNUM,
                            spec_desc = :SPEC_DESC,
                            cust_subsys = :CUST_SUBSYS,
                            sub_desc = :SUB_DESC,
                            location = :LOCATION,
                            persongroup = :PERSONGROUP,
                            pgname = :PGNAME, 
                            schedfinish = to_timestamp(:SCHEDFINISH,'yyyy-mm-dd hh24:mi:ss.ff'),
                            devicenum = :DEVICENUM,
                            sbdesc = :SBDESC,
                            status = :STATUS
                            where wonum = :WONUM
                            `
                            : `update c##wxw.xfjiance
                            set description = :DESCRIPTION,
                            area = :AREA,
                            area2 = :AREA2,
                            ispass = :ISPASS,
                            finishdate = to_timestamp(:FINISHDATE,'yyyy-mm-dd hh24:mi:ss.ff'),
                            person = :PERSON,
                            person2 = :PERSON2,
                            person3 = :PERSON3,
                            remark = :REMARK,
                            cust_linenum = :CUST_LINENUM,
                            cust_specnum = :CUST_SPECNUM,
                            spec_desc = :SPEC_DESC,
                            cust_subsys = :CUST_SUBSYS,
                            sub_desc = :SUB_DESC,
                            location = :LOCATION,
                            persongroup = :PERSONGROUP,
                            pgname = :PGNAME,
                            schedfinish = to_timestamp(:SCHEDFINISH,'yyyy-mm-dd hh24:mi:ss.ff'),
                            devicenum = :DEVICENUM,
                            sbdesc = :SBDESC,
                            status = :STATUS
                            where wonum = :WONUM
                        `
        
    }
    if(type === 'add' || type === 'copy'){
        msgType = "新增"
        editData = FINISHDATE === "" ? [DESCRIPTION,AREA,AREA2,ISPASS,PERSON,PERSON2,PERSON3,REMARK,CUST_LINENUM,CUST_SPECNUM,SPEC_DESC,
                                        CUST_SUBSYS,SUB_DESC,LOCATION,PERSONGROUP,PGNAME,SCHEDFINISH,DEVICENUM,SBDESC,Number(STATUS)]
                                    :   
                                    [DESCRIPTION,AREA,AREA2,ISPASS,FINISHDATE,PERSON,PERSON2,PERSON3,REMARK,CUST_LINENUM,CUST_SPECNUM,SPEC_DESC,
                                    CUST_SUBSYS,SUB_DESC,LOCATION,PERSONGROUP,PGNAME,SCHEDFINISH,DEVICENUM,SBDESC,Number(STATUS)]
        editSql = FINISHDATE === "" ? `insert into c##wxw.xfjiance(wonum,description,area,area2,ispass,person,person2,person3,remark,
                                    cust_linenum,cust_specnum,spec_desc,cust_subsys,sub_desc,location,persongroup,pgname,schedfinish,devicenum,sbdesc,status)
                                    values('',:DESCRIPTION,:AREA,:AREA2,:ISPASS,:PERSON,:PERSON2,:PERSON3,:REMARK,
                                    :CUST_LINENUM,:CUST_SPECNUM,:SPEC_DESC,:CUST_SUBSYS,:SUB_DESC,:LOCATION,:PERSONGROUP,:PGNAME
                                    ,to_timestamp(:SCHEDFINISH,'yyyy-mm-dd hh24:mi:ss.ff'),:DEVICENUM,:SBDESC,:STATUS)
                                    `
                                    :`insert into c##wxw.xfjiance(wonum,description,area,area2,ispass,finishdate,person,person2,person3,remark,
                                    cust_linenum,cust_specnum,spec_desc,cust_subsys,sub_desc,location,persongroup,pgname,schedfinish,devicenum,sbdesc,status)
                                    values('',:DESCRIPTION,:AREA,:AREA2,:ISPASS,to_timestamp(:FINISHDATE,'yyyy-mm-dd hh24:mi:ss.ff'),:PERSON,:PERSON2,:PERSON3,:REMARK,
                                    :CUST_LINENUM,:CUST_SPECNUM,:SPEC_DESC,:CUST_SUBSYS,:SUB_DESC,:LOCATION,:PERSONGROUP,:PGNAME
                                    ,to_timestamp(:SCHEDFINISH,'yyyy-mm-dd hh24:mi:ss.ff'),:DEVICENUM,:SBDESC,:STATUS)
                                    `
    }
    if(type === 'delete'){
        msgType = "删除"
        editData = [Number(WONUM)]
        editSql = `delete from c##wxw.xfjiance where wonum = :WONUM`
    }

    // console.log(editData)
    
    try{
        let newJcData = await db.writeTab(editSql,editData)
        resDatas = {newJcData}
        msg = resDatas.newJcData.errorNum ? `${msgType}失败` : `${msgType}成功`
        resStatus = resDatas.newJcData.errorNum ? 0 : 1
        res.json({
            status:resStatus,
            msg,
            resDatas
        })
    }catch(err){
        console.log(err)
        res.json({
            status:0,
            msg:`${msgType}失败`,
            resDatas:{err}
        })
    }
}


//批量处理检测记录
exports.batchEditJcDatas = async (req,res)=>{
    // console.log(req.body);
    let {type,form} = req.body
    let options = {
        autoCommit: true
    }
    let resDatas
    let msg
    let msgType
    let resStatus
    let editSql
    if(type === "batchDel"){
        msgType = "批量删除"
        editSql = `delete from c##wxw.xfjiance where wonum = :WONUM`
    }
    if(type === "batchUpdate"){
        msgType = "批量修改"
        editSql = `update c##wxw.xfjiance 
                    set description = :DESCRIPTION,
                    area = :AREA,
                    area2 = :AREA2,
                    ispass = :ISPASS,
                    finishdate = to_timestamp(:FINISHDATE,'yyyy-mm-dd hh24:mi:ss.ff'),
                    person = :PERSON,
                    person2 = :PERSON2,
                    person3 = :PERSON3,
                    remark = :REMARK,
                    cust_linenum = :CUST_LINENUM,
                    cust_specnum = :CUST_SPECNUM,
                    spec_desc = :SPEC_DESC,
                    cust_subsys = :CUST_SUBSYS,
                    sub_desc = :SUB_DESC,
                    location = :LOCATION,
                    persongroup = :PERSONGROUP,
                    pgname = :PGNAME,
                    schedfinish = to_timestamp(:SCHEDFINISH,'yyyy-mm-dd hh24:mi:ss.ff'),
                    devicenum = :DEVICENUM,
                    sbdesc = :SBDESC,
                    status = :STATUS
                    where wonum = :WONUM
                    `
    }
    if(type === "batchAdd"){
        msgType = "批量新增"
        editSql = `insert into c##wxw.xfjiance(wonum,description,area,area2,ispass,finishdate,person,person2,person3,remark,
                    cust_linenum,cust_specnum,spec_desc,cust_subsys,sub_desc,location,persongroup,pgname,schedfinish,devicenum,sbdesc,status)
                    values(:WONUM,:DESCRIPTION,:AREA,:AREA2,:ISPASS,:FINISHDATE,:PERSON,:PERSON2,:PERSON3,:REMARK,
                    :CUST_LINENUM,:CUST_SPECNUM,:SPEC_DESC,:CUST_SUBSYS,:SUB_DESC,:LOCATION,:PERSONGROUP,:PGNAME
                   ,to_timestamp(:SCHEDFINISH,'yyyy-mm-dd hh24:mi:ss.ff'),:DEVICENUM,:SBDESC,:STATUS)
                   `
    }

    try{
        let newJcDatas = await db.writeManyTab(editSql,form,options)
        resDatas = {newJcDatas}
        msg = resDatas.newJcDatas.errorNum? `${msgType}失败` : `${msgType}成功`
        resStatus = resDatas.newJcDatas.errorNum? 0 : 1
        res.json({
            status:resStatus,
            msg,
            resDatas
        })
    }catch(err){
        console.log(err)
        res.json({
            status:0,
            msg:`${msgType}失败`,
            resDatas:{err}
        })
    }
}